package es.udc.emarketapp.emarket.model.user.dao;

import es.udc.emarketapp.emarket.model.user.vo.UserVO;
import es.udc.fbellas.j2ee.util.exceptions.InstanceNotFoundException;
import es.udc.fbellas.j2ee.util.exceptions.InternalErrorException;
import es.udc.fbellas.j2ee.util.sql.GeneralOperations;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.List;

public abstract class AbstractSQLUserDAO implements SQLUserDAO{


    public boolean exists(Connection connection, String idUser)
            throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT id_usuario FROM usuario "
                    + "WHERE id_usuario = ?";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            preparedStatement.setString(1, idUser);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            return resultSet.next();

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }
    }//exists

    public boolean exists(Connection connection, String idUser, String pass)
            throws InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean existsNifCif(Connection connection, String nifCif)
            throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT nif_cif FROM usuario "
                    + "WHERE nif_cif = ?";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            preparedStatement.setString(1, nifCif);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            return resultSet.next();

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }
    }

    public UserVO findById(Connection connection, String idUser)
            throws InternalErrorException, InstanceNotFoundException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT pass, aprobado, "
                    + "valido, nom_pila, ap1, ap2, nif_cif, dir, ciudad, "
                    + "cp, prov, pais, email, tlf1, tlf2, tipo_usuario "
                    + "FROM usuario  WHERE id_usuario = ? ";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            preparedStatement.setString(1, idUser);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            if (!resultSet.next()) {
                throw new InstanceNotFoundException(idUser,
                        UserVO.class.getName());
            }

            // Get results.
            int i = 1;
            String pass = resultSet.getString(i++);
            boolean aprobado = resultSet.getBoolean(i++);
            boolean valido = resultSet.getBoolean(i++);
            String nomPila= resultSet.getString(i++);
            String ap1= resultSet.getString(i++);
            String ap2= resultSet.getString(i++);
            String nifCif = resultSet.getString(i++);
            String dir = resultSet.getString(i++);
            String ciudad = resultSet.getString(i++);
            String cp = resultSet.getString(i++);
            String prov = resultSet.getString(i++);
            String pais = resultSet.getString(i++);
            String email = resultSet.getString(i++);
            String tlf1 = resultSet.getString(i++);
            String tlf2 = resultSet.getString(i++);
            String tipoUsuario = resultSet.getString(i++);


            // Return the value object.
            return new UserVO(idUser, pass, nomPila, ap1, ap2, nifCif, dir,
                    ciudad, cp, prov, pais, email, tlf1, tlf2, tipoUsuario,
                    aprobado, valido);
        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//findById
    

    public void update(Connection connection, UserVO userVO)
            throws InternalErrorException, InstanceNotFoundException{

         PreparedStatement preparedStatement = null;

        try {
       // Create "preparedStatement".
            String query = "UPDATE usuario "
                    + "SET pass = ?, ultima_act = ?, aprobado = ?, "
                    + "valido = ?, nom_pila = ?, ap1 = ?, "
                    + "ap2 = ?, nif_cif = ?, dir = ?, "
                    + "ciudad = ?, cp = ?, prov = ?, pais = ?, "
                    + "email = ?, tlf1 = ?, tlf2 = ? "
                    + "WHERE id_usuario = ? ";

            preparedStatement = connection.prepareStatement(query);

         //Ahora lo llenamos con los parametros que se quiere
            int i = 1;
            preparedStatement.setString(i++, userVO.getPass());
            preparedStatement.setTimestamp(i++,
                    new Timestamp(Calendar.getInstance().getTime().getTime()));
            preparedStatement.setBoolean(i++, userVO.isAprobado());
            preparedStatement.setBoolean(i++, userVO.isValido());
            preparedStatement.setString(i++, userVO.getNomPila());
            preparedStatement.setString(i++, userVO.getAp1());
            preparedStatement.setString(i++, userVO.getAp2());
            preparedStatement.setString(i++, userVO.getNifCif());
            preparedStatement.setString(i++, userVO.getDir());
            preparedStatement.setString(i++, userVO.getCiudad());
            preparedStatement.setString(i++, userVO.getCp());
            preparedStatement.setString(i++, userVO.getProv());
            preparedStatement.setString(i++, userVO.getPais());
            preparedStatement.setString(i++, userVO.getEmail());
            preparedStatement.setString(i++, userVO.getTlf1());
            preparedStatement.setString(i++, userVO.getTlf2());
            preparedStatement.setString(i++, userVO.getIdUser());


            // Execute query.
            int insertedRows = preparedStatement.executeUpdate();

            if (insertedRows == 0) {
                throw new SQLException("No se pudo insertar la fila a la tabla"
                        + " 'Usuario'");
            }

            if (insertedRows > 1) {
                throw new SQLException("Fila duplicada en la tabla 'Usuario'");
            }

           } catch (SQLException e) {
        	//System.out.println(e);
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//update

    public UserVO findById(Connection connection, String idUser, String pass) throws InternalErrorException, InstanceNotFoundException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void updateEMail(Connection connection, String idUser, String email) throws InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void updatePassword(Connection connection, String idUser, String pass) throws InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void updateAddress(Connection connection, String idUser, String cir, String ciudad, String cp, String prov) throws InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void updateSendAddress(Connection connection, String idUser, String dir, String ciudad, String cp, String prov) throws InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void updatePais(Connection connection, String idUser, String pais) throws InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void updateTel1(Connection connection, String idUser, String tel1) throws InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void updateTel2(Connection connection, String idUser, String tel2) throws InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean isValid(Connection connection, String idUser) throws InstanceNotFoundException, InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void invalidate(Connection connection, String idUser) throws InstanceNotFoundException, InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void remove(Connection connection, String idUser) throws InstanceNotFoundException, InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

}
